﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;

namespace iPhoneBackupViewer
{
    public partial class SQLiteViewerForm : Form
    {
        public SQLiteViewerForm()
        {
            InitializeComponent();
        }

        private SQLiteConnection conn;

        public string FileName { get; set; }

        private void btnTest_Click(object sender, EventArgs e)
        {
            string filename = "../../../data/AddressBook.sqlitedb";
            OpenDatabase(filename);
        }

        private void OpenDatabase(string filename)
        {
            if (this.conn != null)
            {
                this.conn.Dispose();
                this.conn = null;
            }

            string connString = string.Format(
                "Data Source={0};Read Only=True;FailIfMissing=True;",
                filename);

            this.conn = new SQLiteConnection(connString);
            conn.Open();

            // Fill the tables list.
            using (DataTable dt = conn.GetSchema("Tables"))
            {
                lstTables.Items.AddRange(
                    (from DataRow row in dt.Rows
                     where row["TABLE_TYPE"] as string == "table"
                     let name = row["TABLE_NAME"] as string
                     where name != "_SqliteDatabaseProperties"
                     orderby name
                     select name).ToArray());
            }
        }

        private void SQLiteViewerForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            if (this.conn != null)
                this.conn.Dispose();
        }

        private void lstTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            string table = lstTables.SelectedItem as string;
            if (table == null)
                return;
            string sql = "SELECT * FROM " + table;
            using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                DataTable schema = reader.GetSchemaTable();
                lvFields.Items.Clear();
                lvFields.Items.AddRange(
                    (from DataRow row in schema.Rows
                     let name = row["ColumnName"].ToString()
                     let type = row["DataType"].ToString()
                     let k = type.LastIndexOf('.')
                     select new ListViewItem(new string[] { 
                         name,
                         type.Substring(k + 1)
                     })).ToArray());

                DataTable dt = new DataTable();
                dt.Load(reader);

                // If there are many rows in the data table, adjusting the
                // columns of the grid view can take a long time. In these
                // cases, first populate the grid view with the first 100
                // records in the data table to adjust the column widths,
                // and then populate it with the whole data set.
                if (dt.Rows.Count <= 100)
                {
                    dataGridView1.DataSource = dt;
                    dataGridView1.AutoResizeColumns();
                }
                else
                {
                    var sample = dt.Rows.Cast<DataRow>().Take(100).CopyToDataTable();
                    dataGridView1.DataSource = sample;
                    dataGridView1.AutoResizeColumns();
                    dataGridView1.DataSource = dt;
                }
            }
        }

        private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            // MessageBox.Show(e.Exception.Message);
        }

        private void SQLiteViewerForm_Load(object sender, EventArgs e)
        {
            if (this.FileName != null)
                OpenDatabase(this.FileName);
        }
    }
}
